Skip Headers

Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Recovering NOLOGGING Tables and Indexes: Scenario

You can create tables and indexes with the `CREATE` `TABLE` `AS` `SELECT` statement. You can also specify that the database create them with the `NOLOGGING` option. When you create a table or index as `NOLOGGING`, the database does not generate redo log records for the operation. Thus, you cannot recover objects created with `NOLOGGING`, even if you are running in `ARCHIVELOG` mode.

Note: If you cannot afford to lose tables or indexes created with `NOLOGGING`, then make a backup after the unrecoverable table or index is created.

Be aware that when you perform media recovery, and some tables or indexes are created normally whereas others are created with the `NOLOGGING` option, the `NOLOGGING` objects are marked logically corrupt by the `RECOVER` operation. Any attempt to access the unrecoverable objects returns an `ORA-01578` error message. Drop the `NOLOGGING` objects and re-create them if needed.

Because it is possible to create a table with the `NOLOGGING` option and then create an index with the `LOGGING` option on that table, the index is not marked as logically corrupt after you perform media recovery. The table was unrecoverable (and thus marked as corrupt after recovery), however, so the index points to corrupt blocks. The index must be dropped, and the table and index must be re-created if necessary.

See Also: Oracle Data Guard Concepts and Administration for information about the impact of `NOLOGGING` on a b database